﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GAMS.MODEL;
using KPIS.DBM;
using System.Configuration;
using System.Data;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_MAS_BaseAccount
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public M_ACM_MAS_BaseAccount GetList()
        {
            DataRowCollection drc = null;
            M_ACM_MAS_BaseAccount info = new M_ACM_MAS_BaseAccount();
            try
            {
                dbManager.Open();

                string strSQL = "SELECT cash_account_seq, dn_cr_account_seq, loan_account_seq"
                    + ", payroll_tax_account_seq, remain_account_seq, penalty_account_seq, over_account_seq"
                    + ", direct_pay_account_seq, income_bb_account_seq, redemption_account_seq"
                    + ", collateral_account_seq1, collateral_account_seq2, collateral_account_seq3, collateral_account_seq4"
                    + ", pay_income_account_seq, gov_income_account_seq, gov_deposit_account_seq, wait_account_seq"
                    + ", wait_income_bb_account_seq, revenue_vat_debtor_account_seq, revenue_vat_creditor_account_seq"
                    + ", buy_vat_account_seq, sale_vat_account_seq, personal_tax_account_seq, company_tax_account_seq"
                    + ", buy_v_sale_vat_account_seq, suspense_input_tax_account_seq"
                    + ", close_income_account_seq, close_outcome_account_seq"
                    + ", close_capital_account_seq, close_income_outcome_account_seq"
                    + ", cross_budget_expense_account_seq, cross_budget_income_account_seq, dispose_account_seq"
                    + " FROM acm_mas_base_account";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.cash_account_seq = cn.ConvertNullInt(drc[0]["cash_account_seq"]);
                    info.dn_cr_account_seq = cn.ConvertNullInt(drc[0]["dn_cr_account_seq"]);
                    info.loan_account_seq = cn.ConvertNullInt(drc[0]["loan_account_seq"]);
                    info.payroll_tax_account_seq = cn.ConvertNullInt(drc[0]["payroll_tax_account_seq"]);
                    info.remain_account_seq = cn.ConvertNullInt(drc[0]["remain_account_seq"]);
                    info.penalty_account_seq = cn.ConvertNullInt(drc[0]["penalty_account_seq"]);
                    info.over_account_seq = cn.ConvertNullInt(drc[0]["over_account_seq"]);
                    info.direct_pay_account_seq = cn.ConvertNullInt(drc[0]["direct_pay_account_seq"]);
                    info.income_bb_account_seq = cn.ConvertNullInt(drc[0]["income_bb_account_seq"]);
                    info.redemption_account_seq = cn.ConvertNullInt(drc[0]["redemption_account_seq"]);
                    info.collateral_account_seq1 = cn.ConvertNullInt(drc[0]["collateral_account_seq1"]);
                    info.collateral_account_seq2 = cn.ConvertNullInt(drc[0]["collateral_account_seq2"]);
                    info.collateral_account_seq3 = cn.ConvertNullInt(drc[0]["collateral_account_seq3"]);
                    info.collateral_account_seq4 = cn.ConvertNullInt(drc[0]["collateral_account_seq4"]);
                    info.pay_income_account_seq = cn.ConvertNullInt(drc[0]["pay_income_account_seq"]);
                    info.gov_income_account_seq = cn.ConvertNullInt(drc[0]["gov_income_account_seq"]);
                    info.gov_deposit_account_seq = cn.ConvertNullInt(drc[0]["gov_deposit_account_seq"]);
                    info.wait_account_seq = cn.ConvertNullInt(drc[0]["wait_account_seq"]);
                    info.wait_income_bb_account_seq = cn.ConvertNullInt(drc[0]["wait_income_bb_account_seq"]);
                    info.revenue_vat_creditor_account_seq = cn.ConvertNullInt(drc[0]["revenue_vat_creditor_account_seq"]);
                    info.revenue_vat_debtor_account_seq = cn.ConvertNullInt(drc[0]["revenue_vat_debtor_account_seq"]);
                    info.buy_vat_account_seq = cn.ConvertNullInt(drc[0]["buy_vat_account_seq"]);
                    info.sale_vat_account_seq = cn.ConvertNullInt(drc[0]["sale_vat_account_seq"]);
                    info.personal_tax_account_seq = cn.ConvertNullInt(drc[0]["personal_tax_account_seq"]);
                    info.company_tax_account_seq = cn.ConvertNullInt(drc[0]["company_tax_account_seq"]);
                    info.buy_v_sale_vat_account_seq = cn.ConvertNullInt(drc[0]["buy_v_sale_vat_account_seq"]);
                    info.suspense_input_tax_account_seq = cn.ConvertNullInt(drc[0]["suspense_input_tax_account_seq"]);
                    info.close_income_account_seq = cn.ConvertNullInt(drc[0]["close_income_account_seq"]);
                    info.close_outcome_account_seq = cn.ConvertNullInt(drc[0]["close_outcome_account_seq"]);
                    info.close_capital_account_seq = cn.ConvertNullInt(drc[0]["close_capital_account_seq"]);
                    info.close_income_outcome_account_seq = cn.ConvertNullInt(drc[0]["close_income_outcome_account_seq"]);
                    info.cross_budget_expense_account_seq = cn.ConvertNullInt(drc[0]["cross_budget_expense_account_seq"]);
                    info.cross_budget_income_account_seq = cn.ConvertNullInt(drc[0]["cross_budget_income_account_seq"]);
                    info.dispose_account_seq = cn.ConvertNullInt(drc[0]["dispose_account_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return info;
        }
        public string GetMonth(string month)
        {

            M_ACM_MAS_BaseAccount info = new M_ACM_MAS_BaseAccount();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Month", month);

                string strSQL = "SELECT max(Month(updated_when)) FROM acm_mas_base_account where MONTH(updated_when)=@Month ";
                   
                    

               
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                else
                {
                    return "0";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
      
        public void UPDATE(M_ACM_MAS_BaseAccount info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(36);
                dbManager.AddParameters(0, "@cash_account_seq", info.cash_account_seq);
                dbManager.AddParameters(1, "@dn_cr_account_seq", info.dn_cr_account_seq);
                dbManager.AddParameters(2, "@loan_account_seq", info.loan_account_seq);
                dbManager.AddParameters(3, "@payroll_tax_account_seq", info.payroll_tax_account_seq);
                dbManager.AddParameters(4, "@remain_account_seq", info.remain_account_seq);
                dbManager.AddParameters(5, "@penalty_account_seq", info.penalty_account_seq);
                dbManager.AddParameters(6, "@over_account_seq", info.over_account_seq);
                dbManager.AddParameters(7, "@direct_pay_account_seq", info.direct_pay_account_seq);
                dbManager.AddParameters(8, "@income_bb_account_seq", info.income_bb_account_seq);
                dbManager.AddParameters(9, "@redemption_account_seq", info.redemption_account_seq);
                dbManager.AddParameters(10, "@collateral_account_seq1", info.collateral_account_seq1);
                dbManager.AddParameters(11, "@collateral_account_seq2", info.collateral_account_seq2);
                dbManager.AddParameters(12, "@collateral_account_seq3", info.collateral_account_seq3);
                dbManager.AddParameters(13, "@collateral_account_seq4", info.collateral_account_seq4);
                dbManager.AddParameters(14, "@pay_income_account_seq", info.pay_income_account_seq);
                dbManager.AddParameters(15, "@gov_income_account_seq", info.gov_income_account_seq);
                dbManager.AddParameters(16, "@gov_deposit_account_seq", info.gov_deposit_account_seq);
                dbManager.AddParameters(17, "@wait_account_seq", info.wait_account_seq);
                dbManager.AddParameters(18, "@wait_income_bb_account_seq", info.wait_income_bb_account_seq);
                dbManager.AddParameters(19, "@updated_by", info.updated_by);
                dbManager.AddParameters(20, "@updated_when", info.updated_when);
                dbManager.AddParameters(21, "@revenue_vat_creditor_account_seq", info.revenue_vat_creditor_account_seq);
                dbManager.AddParameters(22, "@revenue_vat_debtor_account_seq", info.revenue_vat_debtor_account_seq);
                dbManager.AddParameters(23, "@buy_vat_account_seq", info.buy_vat_account_seq);
                dbManager.AddParameters(24, "@sale_vat_account_seq", info.sale_vat_account_seq);
                dbManager.AddParameters(25, "@personal_tax_account_seq", info.personal_tax_account_seq);
                dbManager.AddParameters(26, "@company_tax_account_seq", info.company_tax_account_seq);
                dbManager.AddParameters(27, "@buy_v_sale_vat_account_seq", info.buy_v_sale_vat_account_seq);
                dbManager.AddParameters(28, "@suspense_input_tax_account_seq", info.suspense_input_tax_account_seq);
                dbManager.AddParameters(29, "@close_income_account_seq", info.close_income_account_seq);
                dbManager.AddParameters(30, "@close_outcome_account_seq", info.close_outcome_account_seq);
                dbManager.AddParameters(31, "@close_capital_account_seq", info.close_capital_account_seq);
                dbManager.AddParameters(32, "@close_income_outcome_account_seq", info.close_income_outcome_account_seq);
                dbManager.AddParameters(33, "@cross_budget_expense_account_seq", info.cross_budget_expense_account_seq);
                dbManager.AddParameters(34, "@cross_budget_income_account_seq", info.cross_budget_income_account_seq);
                dbManager.AddParameters(35, "@dispose_account_seq", info.dispose_account_seq);

                strSQL = "UPDATE acm_mas_base_account"
                         + " SET cash_account_seq = @cash_account_seq"
                         + ", dn_cr_account_seq = @dn_cr_account_seq"
                         + ", loan_account_seq = @loan_account_seq "
                         + ", payroll_tax_account_seq = @payroll_tax_account_seq"
                         + ", remain_account_seq = @remain_account_seq"
                         + ", penalty_account_seq = @penalty_account_seq"
                         + ", over_account_seq = @over_account_seq"
                         + ", direct_pay_account_seq = @direct_pay_account_seq"
                         + ", income_bb_account_seq = @income_bb_account_seq"
                         + ", redemption_account_seq = @redemption_account_seq"
                         + ", collateral_account_seq1 = @collateral_account_seq1"
                         + ", collateral_account_seq2 = @collateral_account_seq2"
                         + ", collateral_account_seq3 = @collateral_account_seq3"
                         + ", collateral_account_seq4 = @collateral_account_seq4"
                         + ", pay_income_account_seq = @pay_income_account_seq"
                         + ", gov_income_account_seq = @gov_income_account_seq"
                         + ", gov_deposit_account_seq = @gov_deposit_account_seq"
                         + ", wait_account_seq = @wait_account_seq"
                         + ", wait_income_bb_account_seq = @wait_income_bb_account_seq"
                         + ", revenue_vat_creditor_account_seq = @revenue_vat_creditor_account_seq"
                         + ", revenue_vat_debtor_account_seq = @revenue_vat_debtor_account_seq"
                         + ", buy_vat_account_seq = @buy_vat_account_seq"
                         + ", sale_vat_account_seq = @sale_vat_account_seq"
                         + ", personal_tax_account_seq = @personal_tax_account_seq"
                         + ", company_tax_account_seq = @company_tax_account_seq"
                         + ", buy_v_sale_vat_account_seq = @buy_v_sale_vat_account_seq"
                         + ", suspense_input_tax_account_seq = @suspense_input_tax_account_seq"
                         + ", close_income_account_seq = @close_income_account_seq"
                         + ", close_outcome_account_seq = @close_outcome_account_seq"
                         + ", close_capital_account_seq = @close_capital_account_seq"
                         + ", close_income_outcome_account_seq = @close_income_outcome_account_seq"
                         + ", cross_budget_expense_account_seq = @cross_budget_expense_account_seq"
                         + ", cross_budget_income_account_seq = @cross_budget_income_account_seq"
                         + ", dispose_account_seq = @dispose_account_seq"
                         + ", updated_by = @updated_by"
                         + ", updated_when = @updated_when";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void INSERT(M_ACM_MAS_BaseAccount info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(36);
                dbManager.AddParameters(0, "@cash_account_seq", info.cash_account_seq);
                dbManager.AddParameters(1, "@dn_cr_account_seq", info.dn_cr_account_seq);
                dbManager.AddParameters(2, "@loan_account_seq", info.loan_account_seq);
                dbManager.AddParameters(3, "@payroll_tax_account_seq", info.payroll_tax_account_seq);
                dbManager.AddParameters(4, "@remain_account_seq", info.remain_account_seq);
                dbManager.AddParameters(5, "@penalty_account_seq", info.penalty_account_seq);
                dbManager.AddParameters(6, "@over_account_seq", info.over_account_seq);
                dbManager.AddParameters(7, "@direct_pay_account_seq", info.direct_pay_account_seq);
                dbManager.AddParameters(8, "@income_bb_account_seq", info.income_bb_account_seq);
                dbManager.AddParameters(9, "@redemption_account_seq", info.redemption_account_seq);
                dbManager.AddParameters(10, "@collateral_account_seq1", info.collateral_account_seq1);
                dbManager.AddParameters(11, "@collateral_account_seq2", info.collateral_account_seq2);
                dbManager.AddParameters(12, "@collateral_account_seq3", info.collateral_account_seq3);
                dbManager.AddParameters(13, "@collateral_account_seq4", info.collateral_account_seq4);
                dbManager.AddParameters(14, "@pay_income_account_seq", info.pay_income_account_seq);
                dbManager.AddParameters(15, "@gov_income_account_seq", info.gov_income_account_seq);
                dbManager.AddParameters(16, "@gov_deposit_account_seq", info.gov_deposit_account_seq);
                dbManager.AddParameters(17, "@wait_account_seq", info.wait_account_seq);
                dbManager.AddParameters(18, "@wait_income_bb_account_seq", info.wait_income_bb_account_seq);
                dbManager.AddParameters(19, "@updated_by", info.updated_by);
                dbManager.AddParameters(20, "@updated_when", info.updated_when);
                dbManager.AddParameters(21, "@revenue_vat_creditor_account_seq", info.revenue_vat_creditor_account_seq);
                dbManager.AddParameters(22, "@revenue_vat_debtor_account_seq", info.revenue_vat_debtor_account_seq);
                dbManager.AddParameters(23, "@buy_vat_account_seq", info.buy_vat_account_seq);
                dbManager.AddParameters(24, "@sale_vat_account_seq", info.sale_vat_account_seq);
                dbManager.AddParameters(25, "@personal_tax_account_seq", info.personal_tax_account_seq);
                dbManager.AddParameters(26, "@company_tax_account_seq", info.company_tax_account_seq);
                dbManager.AddParameters(27, "@buy_v_sale_vat_account_seq", info.buy_v_sale_vat_account_seq);
                dbManager.AddParameters(28, "@suspense_input_tax_account_seq", info.suspense_input_tax_account_seq);
                dbManager.AddParameters(29, "@close_income_account_seq", info.close_income_account_seq);
                dbManager.AddParameters(30, "@close_outcome_account_seq", info.close_outcome_account_seq);
                dbManager.AddParameters(31, "@close_capital_account_seq", info.close_capital_account_seq);
                dbManager.AddParameters(32, "@close_income_outcome_account_seq", info.close_income_outcome_account_seq);
                dbManager.AddParameters(33, "@cross_budget_expense_account_seq", info.cross_budget_expense_account_seq);
                dbManager.AddParameters(34, "@cross_budget_income_account_seq", info.cross_budget_income_account_seq);
                dbManager.AddParameters(35, "@dispose_account_seq", info.dispose_account_seq);

                strSQL = "INSERT INTO acm_mas_base_account"
                         + "(cash_account_seq"
                         + ", dn_cr_account_seq"
                         + ", loan_account_seq"
                         + ", payroll_tax_account_seq"
                         + ", remain_account_seq "
                         + ", penalty_account_seq"
                         + ", over_account_seq"
                         + ", direct_pay_account_seq"
                         + ", income_bb_account_seq "
                         + ", redemption_account_seq "
                         + ", collateral_account_seq1"
                         + ", collateral_account_seq2"
                         + ", collateral_account_seq3"
                         + ", collateral_account_seq4"
                         + ", pay_income_account_seq"
                         + ", gov_income_account_seq"
                         + ", gov_deposit_account_seq"
                         + ", wait_account_seq"
                         + ", wait_income_bb_account_seq"
                         + ", revenue_vat_creditor_account_seq"
                         + ", revenue_vat_debtor_account_seq"
                         + ", buy_vat_account_seq"
                         + ", sale_vat_account_seq"
                         + ", personal_tax_account_seq"
                         + ", company_tax_account_seq"
                         + ", buy_v_sale_vat_account_seq"
                         + ", suspense_input_tax_account_seq"
                         + ", close_income_account_seq"
                         + ", close_outcome_account_seq"
                         + ", close_capital_account_seq"
                         + ", close_income_outcome_account_seq"
                         + ", cross_budget_expense_account_seq"
                         + ", cross_budget_income_account_seq"
                         + ", dispose_account_seq"

                         + ", updated_by"
                         + ", updated_when)"
                         + "VALUES("
                         + "@cash_account_seq,@dn_cr_account_seq,@loan_account_seq,@payroll_tax_account_seq"
                         + ",@remain_account_seq,@penalty_account_seq,@over_account_seq,@direct_pay_account_seq"
                         + ",@income_bb_account_seq,@redemption_account_seq,@collateral_account_seq1,@collateral_account_seq2"
                         + ",@collateral_account_seq3,@collateral_account_seq4,@pay_income_account_seq,@gov_income_account_seq,@gov_deposit_account_seq"
                         + ",@wait_account_seq,@wait_income_bb_account_seq,@revenue_vat_creditor_account_seq,@revenue_vat_debtor_account_seq,@buy_vat_account_seq"
                         + ",@sale_vat_account_seq,@personal_tax_account_seq,@company_tax_account_seq,@buy_v_sale_vat_account_seq"
                         + ", @suspense_input_tax_account_seq, @close_income_account_seq, @close_outcome_account_seq"
                         + ", @close_capital_account_seq, @close_income_outcome_account_seq"
                         + ", @cross_budget_expense_account_seq, @cross_budget_income_account_seq, @dispose_account_seq"

                         + ",@updated_by,@updated_when"
                         +")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
